package jp.ac.aiit.yc.softarch.hbcp.service.common;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.List;
import jp.ac.aiit.yc.softarch.hbcp.model.common.User;
import jp.ac.aiit.yc.softarch.hbcp.model.kj.Card;
import jp.ac.aiit.yc.softarch.hbcp.model.kj.History;

public class DataAccessObject {//データベースアクセスを担当するクラス。このクラスを通さないDBアクセスはさせない。
	static Connection connection;
	static Statement stmt;
	static PreparedStatement pstmt;
	static ResultSet rs;

	static {
		try{//全体で一つのコネクションを共有する。パフォーマンスとか問題あるかなあ？？
		Class.forName("org.hsqldb.jdbcDriver");
		connection = DriverManager.getConnection("jdbc:hsqldb:file:/HBCP_DB/hbcp_dbFile;shutdown=true", "sa", null);

		}catch(Exception e){
			System.out.println(e);
		}
	}

	public static List<User> readUsers()throws Exception{//全ユーザー情報をDBから読み込むメソッド
		List<User> users = new ArrayList<User>();
		stmt = connection.createStatement();
		rs = stmt.executeQuery("select * from user");
		System.out.println("doing DataAccessObject.readUsers() : " + CalendarUtil.nowDate());
		while(rs.next()) {
			System.out.print(rs.getString("name"));
			System.out.print("\t");
			System.out.println(rs.getString("password"));
			users.add(new User(rs.getString("name"),rs.getString("password")));
		}
		rs.close();
		stmt.close();
		return users;
	}

	public static void insertUser(User user)throws Exception{//ユーザーをDBに登録するメソッド
		System.out.println("doing DataAccessObject.insertUser() : " + CalendarUtil.nowDate());
		pstmt = connection.prepareStatement("INSERT INTO USER VALUES(?,?)");
		pstmt.setString(1,user.name );
		pstmt.setString(2,user.password) ;
		pstmt.executeUpdate();
		pstmt.close();
		return ;
	}

	public static void updateUser(User user)throws Exception{//DBのユーザー情報を更新するメソッド
		System.out.println("doing DataAccessObject.updateUser() : " + CalendarUtil.nowDate());
		pstmt = connection.prepareStatement("UPDATE USER SET password = ? WHERE name = ?");
		pstmt.setString(1,user.password);
		pstmt.setString(2,user.name);
		pstmt.executeUpdate();
		pstmt.close();
		return ;
	}
	public static void deleteUser(User user)throws Exception{//DBのユーザー情報を削除するメソッド
		System.out.println("doing deleteUser() : " + CalendarUtil.nowDate());
		pstmt = connection.prepareStatement("DELETE FROM USER WHERE name = ?");
		pstmt.setString(1,user.name);
		pstmt.executeUpdate();
		pstmt.close();
		return ;
	}

	public static List<Card> readCards()throws Exception{//全カード情報をDBから読み込むメソッド
		List<Card> cards = new ArrayList<Card>();
		stmt = connection.createStatement();
		rs = stmt.executeQuery("select * from card");
		System.out.println("doing DataAccessObject.readCards() : " + CalendarUtil.nowDate());

		while(rs.next()) {
			System.out.print(rs.getString("title"));
			System.out.print("\t");
			System.out.print(rs.getString("content"));
			System.out.print("\t");
			System.out.print(rs.getInt("x"));
			System.out.print("\t");
			System.out.print(rs.getInt("y"));
			System.out.print("\t");
			System.out.print(rs.getString("uid"));
			System.out.print("\t");
			System.out.print(rs.getString("owner"));
			System.out.print("\t");
			System.out.println(rs.getString("color"));
			System.out.print("\t");
			System.out.println(rs.getInt("width"));
			System.out.print("\t");
			System.out.println(rs.getInt("height"));
			cards.add(new Card(rs.getString("content"),rs.getString("title"),rs.getInt("x"),rs.getInt("y"),rs.getString("uid"),new User(rs.getString("owner"),"password"),rs.getString("color"),rs.getInt("width"),rs.getInt("height")));
		}
		rs.close();
		stmt.close();
		return cards;
	}
	public static void insertCard(Card card)throws Exception{//カードをDBに登録するメソッド
		System.out.println("doing DataAccessObject.insertCard() : " + CalendarUtil.nowDate());
		pstmt = connection.prepareStatement("INSERT INTO CARD VALUES(?,?,?,?,?,?,?,?,?)");
		pstmt.setString(1,card.getTitle() );
		pstmt.setString(2,card.getContent()) ;
		pstmt.setInt(3,card.getX() );
		pstmt.setInt(4,card.getY()) ;
		pstmt.setString(5,card.getUid() );
		pstmt.setString(6,card.getOwner().name) ;
		pstmt.setString(7,card.getColor());
		pstmt.setInt(8,card.getWidth()) ;
		pstmt.setInt(9,card.getHeight());

		pstmt.executeUpdate();
		pstmt.close();
		return ;
	}
	public static void insertCardOpHistory(Card card,String op,String userName)throws Exception{//カード操作の履歴をDBに登録するメソッド
		System.out.println("doing DataAccessObject.insertCardOpHistory() : " + CalendarUtil.nowDate());
		pstmt = connection.prepareStatement("INSERT INTO CARDOPHISTORY VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?)");
		stmt = connection.createStatement();
		rs = stmt.executeQuery("select max(id) as maxid from CARDOPHISTORY");
		int newID = 1;
		if(rs.next()){
			newID = rs.getInt("maxid") + 1;
		}
		pstmt.setInt(1,newID);
		pstmt.setString(2,op );
		pstmt.setString(3,CalendarUtil.nowDate() );
		pstmt.setString(4,userName );
		pstmt.setString(5,card.getTitle() );
		pstmt.setString(6,card.getContent()) ;
		pstmt.setInt(7,card.getX() );
		pstmt.setInt(8,card.getY()) ;
		pstmt.setString(9,card.getUid() );
		pstmt.setString(10,card.getOwner().name) ;
		pstmt.setString(11,card.getColor());
		pstmt.setInt(12,card.getWidth());
		pstmt.setInt(13,card.getHeight());
		pstmt.executeUpdate();
		pstmt.close();
		rs.close();
		stmt.close();
		return ;
	}


	public static List<History> readHistory()throws Exception{//全ヒストリ情報をDBから読み込むメソッド
		List<History> history = new ArrayList<History>();
		stmt = connection.createStatement();
		rs = stmt.executeQuery("select * from CARDOPHISTORY");
		System.out.println("doing DataAccessObject.readHistory() : " + CalendarUtil.nowDate());

		while(rs.next()) {
			System.out.print(rs.getInt("id"));
			System.out.print("\t");
			System.out.print(rs.getString("op"));
			System.out.print("\t");
			System.out.print(rs.getString("username"));
			System.out.print("\t");
			System.out.println(rs.getString("date"));

			history.add(new History(rs.getInt("id"),rs.getString("title"),rs.getString("content"),rs.getString("op"),rs.getString("username"),rs.getString("date")));
		}
		rs.close();
		stmt.close();
		return history;
	}

	public static void updateCard(Card card)throws Exception{//DBのカード情報を更新するメソッド
		System.out.println("doing DataAccessObject.updateCard() : " + CalendarUtil.nowDate());
		pstmt = connection.prepareStatement("UPDATE CARD SET title = ?,content=?,x=?,y=?,owner=?,color=?,width=?,height=? WHERE uid = ?");
		pstmt.setString(1,card.getTitle() );
		pstmt.setString(2,card.getContent()) ;
		pstmt.setInt(3,card.getX() );
		pstmt.setInt(4,card.getY()) ;
		pstmt.setString(5,card.getOwner().name) ;
		pstmt.setString(6,card.getColor());
		pstmt.setInt(7,card.getWidth());
		pstmt.setInt(8,card.getHeight() );
		pstmt.setString(9,card.getUid() );
		pstmt.executeUpdate();
		pstmt.close();
		return ;
	}
	public static void deleteCard(Card card)throws Exception{//DBのカード情報を削除するメソッド
		System.out.println("doing DataAccessObject.deleteCard() : " + CalendarUtil.nowDate());
		pstmt = connection.prepareStatement("DELETE FROM CARD WHERE uid = ?");
		pstmt.setString(1,card.getUid());
		pstmt.executeUpdate();
		pstmt.close();
		return ;
	}
}
